package com.mrzhou.study.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

public class ExcelStudy {

    public static void main(String[] args) throws Exception {
        List<String> list = new ArrayList<>();
        list.add("1");
        list.stream().filter(var -> var.equals("1")).findFirst().map(var -> null).orElse(null);


        // 初始化, 第一周至少四天
//        WeekFields wfs = WeekFields.of(DayOfWeek.MONDAY, 1);
//        int num= LocalDate.of(2021, 2, 31).get(wfs.weekOfWeekBasedYear());
//        System.out.print(num);
//        ExcelStudy study = new ExcelStudy();
//        study.createHeader();
    }

    private XSSFWorkbook workbook = new XSSFWorkbook();
    private final String DATA_MEASURE = "DataMeasure";


    public void createHeader() throws Exception {
        XSSFSheet sheet = workbook.createSheet("poi使用");
        sheet.setDefaultColumnWidth(9); // 设置默认列宽
        sheet.setDefaultRowHeightInPoints(15); // 设置行高

        XSSFRow header = sheet.createRow(0); // 创建表头第一行
        XSSFRow header2 = sheet.createRow(1); // 创建表头第二行

        AtomicInteger item = new AtomicInteger(0);
        System.out.println(getHeaderNames().lastIndexOf(this.DATA_MEASURE));
        getHeaderNames().stream().forEach(var -> {
            int columnIndex = item.getAndIncrement();
            this.headerCell(header, columnIndex, var, this.headerCellStyle());
            this.addHeaderMergedRegion(sheet, 0, 1, columnIndex, columnIndex);
        });

        FileOutputStream fos = new FileOutputStream(new File("C:/Users/EDZ/Desktop/poi.xlsx"));
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    public XSSFCell headerCell(XSSFRow row, int index, String value, CellStyle cellStyle) {
        XSSFCell cell = row.createCell(index);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle); // 设置单元格式
        return cell;
    }

    public XSSFCellStyle defaultDashedCell() {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderRight(BorderStyle.DOTTED);
        return cellStyle;
    }

    public XSSFCellStyle headerCellStyle() {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中

        // 设置背景色
        cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(51, 204, 204))); // 设置背景色为99
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置字体并加粗
        XSSFFont font = workbook.createFont();
        font.setFontName("Calibri");
        font.setBold(true);
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);

        cellStyle.setWrapText(true); // 自动换行

        // 设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        return cellStyle;
    }

    public void addHeaderMergedRegion(XSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress range = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(range);
        RegionUtil.setBorderTop(BorderStyle.THIN, range, sheet);
        RegionUtil.setBorderBottom(BorderStyle.HAIR, range, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, range, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, range, sheet);
    }

    public List<String> getHeaderNames() { /** header按顺序添加 */
        List<String> headers = new ArrayList<>();
        headers.add("Item");
        headers.add("Plant");
        headers.add("H.H P/N");
        headers.add("Desc.");
        headers.add("Purch Group");
        headers.add("Manufacturer");
        headers.add("Manufacturer P/N");
        headers.add("Vendor Code");
        headers.add("L/T(day)");
        headers.add("Unit");
        headers.add("Open Order");
        headers.add("Stock on hand");
        headers.add("WIP");
        headers.add("IQC");
        headers.add("VMI");
        headers.add("CVMI");
        headers.add("MOQ");
        headers.add(this.DATA_MEASURE);
        return headers;
    }

    private static LocalDate getDateByYearAndWeekNumAndDayOfWeek(Integer year, Integer num, DayOfWeek dayOfWeek) {
        //周数小于10在前面补个0
        String numStr = num < 10 ? "0" + String.valueOf(num) : String.valueOf(num);
        //2019-W01-01获取第一周的周一日期，2019-W02-07获取第二周的周日日期
        String weekDate = String.format("%s-W%s-%s", year, numStr, dayOfWeek.getValue());
        return LocalDate.parse(weekDate, DateTimeFormatter.ISO_WEEK_DATE);
    }

    private enum ForecastEnum {

        DATA_MEASURE("DataMeasure"),

        REQUEST_QTY("Request Qty"),

        COMMIT_QTY("Commit Qty"),

        REMARK("Remark"),

        DIF("Dif"),

        DSI("DSI");

        private final String code;

        ForecastEnum(String code) {
            this.code = code;
        }

        public String value() {
            return this.code;
        }

    }

}
